package elite.bussiness.dyn2.client.doLib.sub;

import com.wisdge.dataservice.Result;
import elite.bussiness.dyn2.root.OneDyn2Sqls;
import elite.core.dataBase.DBMethod;
import elite.core.dataBase.sys.IECDS;
import elite.core.errorManager.ErrManager;
import elite.core.monitor.MonitorOne;
import elite.core.monitor.MonitorRoot;
import elite.core.root.*;
import elite.core.staffInfo.OneStaff;
import elite.sysfun.dbfun.ResultSetObjList;
import elite.sysfun.dbfun.sysMdl.OneBoundType;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.text.StringEscapeUtils;

import java.sql.*;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

@Slf4j
public class Dyn2OneSql {
    private LinkedList<String> _SqlArray = new LinkedList<String>();
    private LinkedList<OneBoundType> _BoundList = new LinkedList<OneBoundType>();
    private String _Key;

    public String getKey() {
        return _Key;
    }

    private int CheckFirstKey(String mKey) {
        if (StaticMethod.StringSub(mKey, 0, 2).equals("{C")) {
            int mTmpIndex1 = mKey.indexOf("}");
            if (mTmpIndex1 > -1) {
                String mTmpStr = mKey.substring(2, mTmpIndex1);

                if (mTmpStr.equalsIgnoreCase("_Page_Size") || mTmpStr.equalsIgnoreCase("_Page_Start") || mTmpStr.equalsIgnoreCase("_Page_End") || mTmpStr.equalsIgnoreCase("_GRP") || mTmpStr.equalsIgnoreCase("_BY") || mTmpStr.equalsIgnoreCase("_NOW") || mTmpStr.equalsIgnoreCase("_EPID") || mTmpStr.equalsIgnoreCase("_EPIDTB") || mTmpStr.equalsIgnoreCase("_guid") || mTmpStr.equalsIgnoreCase("_Lang")) {
                    return -1;
                } else if (StaticMethod.StringToInt(mTmpStr) > 0) {
                    return -1;
                } else {
                    return -2;
                }
            } else {
                return -2;
            }
        } else if (StaticMethod.StringSub(mKey, 0, 5).equals("{DSL_")) {
            int mTmpIndex1 = mKey.indexOf("}");
            if (mTmpIndex1 > -1) {
                int iTmp = StaticMethod.StringToInt(mKey.substring(5, mTmpIndex1));
                if (iTmp > 0) {
                    return iTmp;
                } else {
                    return -2;
                }
            } else {
                return -2;
            }
        } else {
            return -2;
        }
    }

    public Dyn2OneSql(String vSql, String vKey) {
        log.debug("Dyn2OneSql vSql:" + vSql + " vKey:" + vKey);
        int mMaxLen;
        int mTmpIndex = vSql.indexOf("{");
        while (mTmpIndex >= 0) {
            int checkResult = CheckFirstKey(vSql.substring(mTmpIndex));
            if (checkResult > 0) {
                int mTmpIndex1 = vSql.indexOf("}", mTmpIndex);
                if (mTmpIndex1 > -1) {
                    _SqlArray.add(vSql.substring(0, mTmpIndex));
                    _SqlArray.add("#O$T#" + checkResult);
                    _BoundList.add(new OneBoundType(String.valueOf(checkResult), 100));
                    vSql = vSql.substring(mTmpIndex1 + 1);
                    mTmpIndex = vSql.indexOf("{");
                } else {
                    mTmpIndex = vSql.indexOf("{", mTmpIndex + 1);
                }
            } else if (checkResult == -1) {
                mMaxLen = vSql.length();
                int mTmpIndex1 = vSql.indexOf("}", mTmpIndex);
                if (mTmpIndex1 >= 0) {
                    if (mTmpIndex > 0) {
                        if (vSql.substring(mTmpIndex - 1, mTmpIndex).equals("@")) {
                            if (mTmpIndex1 + 1 < mMaxLen) {
                                if (vSql.substring(mTmpIndex1 + 1, mTmpIndex1 + 2).equals("@")) {
                                    _SqlArray.add(vSql.substring(0, mTmpIndex - 1));
                                    _SqlArray.add("#P@P#" + vSql.substring(mTmpIndex + 2, mTmpIndex1).trim().toUpperCase());
                                    vSql = vSql.substring(mTmpIndex1 + 2);
                                } else if (vSql.substring(mTmpIndex1 + 1, mTmpIndex1 + 2).equals("$")) {
                                    _SqlArray.add(vSql.substring(0, mTmpIndex - 1));
                                    _SqlArray.add("#P$@" + vSql.substring(mTmpIndex + 2, mTmpIndex1).trim().toUpperCase());
                                } else if (vSql.substring(mTmpIndex1 + 1, mTmpIndex1 + 2).equalsIgnoreCase("L")) {
                                    _SqlArray.add(vSql.substring(0, mTmpIndex - 1));
                                    _SqlArray.add("#P*P#" + vSql.substring(mTmpIndex + 2, mTmpIndex1).trim().toUpperCase());
                                    vSql = vSql.substring(mTmpIndex1 + 2);
                                } else if (vSql.substring(mTmpIndex1 + 1, mTmpIndex1 + 2).equalsIgnoreCase("S")) { //字符串绑定
                                    _BoundList.add(new OneBoundType(vSql.substring(mTmpIndex + 1, mTmpIndex1).trim().toUpperCase(), 0));
                                    vSql = vSql.substring(0, mTmpIndex - 1) + "?" + vSql.substring(mTmpIndex1 + 2);
                                } else if (vSql.substring(mTmpIndex1 + 1, mTmpIndex1 + 2).equalsIgnoreCase("N")) { //数字绑定
                                    _BoundList.add(new OneBoundType(vSql.substring(mTmpIndex + 1, mTmpIndex1).trim().toUpperCase(), 1));
                                    vSql = vSql.substring(0, mTmpIndex - 1) + "?" + vSql.substring(mTmpIndex1 + 2);
                                } else if (vSql.substring(mTmpIndex1 + 1, mTmpIndex1 + 2).equalsIgnoreCase("I")) { //数字绑定
                                    _BoundList.add(new OneBoundType(vSql.substring(mTmpIndex + 1, mTmpIndex1).trim().toUpperCase(), 5));
                                    vSql = vSql.substring(0, mTmpIndex - 1) + "?" + vSql.substring(mTmpIndex1 + 2);
                                } else if (vSql.substring(mTmpIndex1 + 1, mTmpIndex1 + 2).equalsIgnoreCase("D")) { //日期绑定
                                    _BoundList.add(new OneBoundType(vSql.substring(mTmpIndex + 1, mTmpIndex1).trim().toUpperCase(), 0));
                                    vSql = vSql.substring(0, mTmpIndex - 1) + "?" + vSql.substring(mTmpIndex1 + 2);
                                } else {
                                    _SqlArray.add(vSql.substring(0, mTmpIndex));
                                    _SqlArray.add("#P$P#" + vSql.substring(mTmpIndex + 2, mTmpIndex1).trim().toUpperCase());
                                    vSql = vSql.substring(mTmpIndex1 + 1);
                                }
                            } else {
                                _SqlArray.add(vSql.substring(0, mTmpIndex));
                                _SqlArray.add("#P$P#" + vSql.substring(mTmpIndex + 2, mTmpIndex1).trim().toUpperCase());
                                vSql = vSql.substring(mTmpIndex1 + 1);
                            }
                        } else {
                            _SqlArray.add(vSql.substring(0, mTmpIndex));
                            _SqlArray.add("#P$P#" + vSql.substring(mTmpIndex + 2, mTmpIndex1).trim().toUpperCase());
                            vSql = vSql.substring(mTmpIndex1 + 1);
                        }
                    } else {
                        _SqlArray.add(vSql.substring(0, mTmpIndex));
                        _SqlArray.add("#P$P#" + vSql.substring(mTmpIndex + 2, mTmpIndex1).trim().toUpperCase());
                        vSql = vSql.substring(mTmpIndex1 + 1);
                    }
                    mTmpIndex = vSql.indexOf("{");
                } else {
                    mTmpIndex = vSql.indexOf("{", mTmpIndex + 1);
                }
            } else {
                mTmpIndex = vSql.indexOf("{", mTmpIndex + 1);
            }
        }
        _SqlArray.add(vSql);
        log.debug("Dyn2OneSql End _SqlArray:" + _SqlArray);
    }

    public LinkedList<OneBoundType> get_BoundList() {
        return _BoundList;
    }

    @SuppressWarnings("unchecked")
    public ReturnDyn2OneSql ReplacePara(String versionId, Map<String, Object> vValueList, OneDyn2Sqls OneDynRoot, Map<String, Object> oneSqlPara, OneStaff vOneStaff, String epIdInStr, String dbType, Map<String, Object> rootDSL, LogObj logk) {
        ReturnDyn2OneSql mRt = new ReturnDyn2OneSql();
        StringBuffer mTmpSql = new StringBuffer();
        int iCount = _SqlArray.size();
        for (int i = 0; i < iCount; i++) {
            String mTmp = _SqlArray.get(i);
            if (mTmp.length() > 5) {
                if (mTmp.substring(0, 5).equals("#P$P#")) {
                    String mTmpInfo = mTmp.substring(5);
                    if (mTmpInfo.equalsIgnoreCase("_GRP")) {
                        if (vOneStaff == null) {
                            mTmpSql.append("");
                        } else {
                            mTmpSql.append(vOneStaff.getGroupId());
                        }
                    } else if (mTmpInfo.equalsIgnoreCase("_BY")) {
                        if (vOneStaff == null) {
                            mTmpSql.append("");
                        } else {
                            mTmpSql.append(vOneStaff.getStaffId());
                        }
                    } else if (mTmpInfo.equalsIgnoreCase("_NOW")) {
                        mTmpSql.append(StaticMethod.getNowTimeBySql());
                    } else if (mTmpInfo.equalsIgnoreCase("_EPID")) {
                        if (vOneStaff == null) {
                            mTmpSql.append(epIdInStr);
                        } else {
                            mTmpSql.append(vOneStaff.getePID());
                        }
                    } else if (mTmpInfo.equalsIgnoreCase("_EPIDTB")) {
                        if (vOneStaff == null) {
                            if ("".equals("")) {
                                mTmpSql.append("");
                            } else {
                                mTmpSql.append("_" + epIdInStr);
                            }
                        } else {
                            mTmpSql.append(vOneStaff.getdBExtStr());
                        }
                    } else if (mTmpInfo.equalsIgnoreCase("_GUID")) {
                        mTmpSql.append(StaticMethod.getGUID());
                    } else if (mTmpInfo.equalsIgnoreCase("_sfid")) {
                        mTmpSql.append(StaticMethod.getSnowID());
                    } else if (mTmpInfo.equalsIgnoreCase("_Lang")) {
                        if (vOneStaff == null) {
                            mTmpSql.append("");
                        } else {
                            mTmpSql.append(vOneStaff.getLoginLang());
                        }
                    } else {
                        mTmpSql.append(StaticMethod.getMapOneString(vValueList, "C" + mTmp.substring(5)));
                    }
                } else if (mTmp.substring(0, 5).equals("#P@P#")) {
                    String mTmpInfo = mTmp.substring(5);
                    if (mTmpInfo.equalsIgnoreCase("_GRP")) {
                        if (vOneStaff == null) {
                            mTmpSql.append("");
                        } else {
                            mTmpSql.append(vOneStaff.getGroupId().replaceAll("'", "''").replaceAll(" ", ""));
                        }
                    } else if (mTmpInfo.equalsIgnoreCase("_BY")) {
                        if (vOneStaff == null) {
                            mTmpSql.append("");
                        } else {
                            mTmpSql.append(vOneStaff.getStaffId().replaceAll("'", "''"));
                        }
                    } else if (mTmpInfo.equalsIgnoreCase("_NOW")) {
                        mTmpSql.append(StaticMethod.getNowTimeBySql());
                    } else if (mTmpInfo.equalsIgnoreCase("_EPID")) {
                        if (vOneStaff == null) {
                            mTmpSql.append(epIdInStr);
                        } else {
                            mTmpSql.append(vOneStaff.getePID().replaceAll("'", "''").replaceAll(" ", ""));
                        }
                    } else if (mTmpInfo.equalsIgnoreCase("_GUID")) {
                        mTmpSql.append(StaticMethod.getGUID());
                    } else if (mTmpInfo.equalsIgnoreCase("_sfid")) {
                        mTmpSql.append(StaticMethod.getSnowID());
                    } else if (mTmpInfo.equalsIgnoreCase("_EPIDTB")) {
                        if (vOneStaff == null) {
                            if ("".equals("")) {
                                mTmpSql.append("");
                            } else {
                                mTmpSql.append("_" + epIdInStr);
                            }
                        } else {
                            mTmpSql.append(vOneStaff.getdBExtStr().replaceAll("'", "''").replaceAll(" ", ""));
                        }
                    } else if (mTmpInfo.equalsIgnoreCase("_Lang")) {
                        if (vOneStaff == null) {
                            mTmpSql.append("");
                        } else {
                            mTmpSql.append(vOneStaff.getLoginLang().replaceAll("'", "''").replaceAll(" ", ""));
                        }
                    } else {
                        String mtmpStr = StaticMethod.getMapOneString(vValueList, "C" + mTmp.substring(5));
                        if (mtmpStr.trim().length() == 0 || mtmpStr.matches("^[a-z0-9A-Z_.]+$")) {
                            mTmpSql.append(mtmpStr.trim());
                        } else {
                            log.error(logk.getLogStr() + " 不合法的SQL数据入参,name:" + "C" + mTmp.substring(5) + " NN value:" + mtmpStr);
                            mTmpSql.append("#wrongfulString#");
                        }
                    }
                } else if (mTmp.substring(0, 5).equals("#P$@")) {
                    String mTmpInfo = mTmp.substring(5);
                    if (mTmpInfo.equalsIgnoreCase("_GRP")) {
                        if (vOneStaff == null) {
                            mTmpSql.append("");
                        } else {
                            mTmpSql.append(vOneStaff.getGroupId().replaceAll("'", "''").replaceAll(" ", ""));
                        }
                    } else if (mTmpInfo.equalsIgnoreCase("_BY")) {
                        if (vOneStaff == null) {
                            mTmpSql.append("");
                        } else {
                            mTmpSql.append(vOneStaff.getStaffId().replaceAll("'", "''").replaceAll(" ", ""));
                        }
                    } else if (mTmpInfo.equalsIgnoreCase("_NOW")) {
                        mTmpSql.append(StaticMethod.getNowTimeBySql());
                    } else if (mTmpInfo.equalsIgnoreCase("_EPID")) {
                        if (vOneStaff == null) {
                            mTmpSql.append(epIdInStr);
                        } else {
                            mTmpSql.append(vOneStaff.getdBExtStr().replaceAll("'", "''").replaceAll(" ", ""));
                        }
                    } else if (mTmpInfo.equalsIgnoreCase("_EPIDId")) {
                        if (vOneStaff == null) {
                            if ("".equals("")) {
                                mTmpSql.append("");
                            } else {
                                mTmpSql.append("_" + epIdInStr);
                            }
                        } else {
                            mTmpSql.append(vOneStaff.getePID().replaceAll("'", "''").replaceAll(" ", ""));
                        }
                    } else if (mTmpInfo.equalsIgnoreCase("_Lang")) {
                        if (vOneStaff == null) {
                            mTmpSql.append("");
                        } else {
                            mTmpSql.append(vOneStaff.getLoginLang().replaceAll("'", "''").replaceAll(" ", ""));
                        }
                    } else if (mTmpInfo.equalsIgnoreCase("_guid")) {
                        mTmpSql.append(StaticMethod.getGUID());
                    } else if (mTmpInfo.equalsIgnoreCase("_sfid")) {
                        mTmpSql.append(StaticMethod.getSnowID());
                    } else {
                        String mtmpStr = StaticMethod.getMapOneString(vValueList, "C" + mTmp.substring(5)).replaceAll(" ", "");
                        if (mtmpStr.trim().length() == 0 || mtmpStr.matches("^[a-z0-9A-Z_,.]+$")) {
                            mTmpSql.append(mtmpStr.trim());
                        } else {
                            log.error(logk.getLogStr() + " 不合法的SQL数据入参,name:" + "C" + mTmp.substring(5) + " NN value:" + mtmpStr);
                            mTmpSql.append("#wrongfulString$#");
                        }
                    }
                } else if (mTmp.substring(0, 5).equals("#P*P#")) {
                    mTmpSql.append(StaticMethod.mapOneToSqlInList(vValueList, "C" + mTmp.substring(5)));
                    //mTmpSql.append(StaticMethod.stringToSqlInList(StaticMethod.getMapOneString(vValueList, "C"+mTmp.substring(5))));
                } else if (mTmp.substring(0, 5).equals("#O$T#")) { //SQL 片断
                    if (oneSqlPara == null) {
                        //无片断信息
                    } else {
                        int mOutIndex = StaticMethod.StringToInt(mTmp.substring(5));
                        if (mRt.OneOutExists(String.valueOf(mOutIndex))) {
                            OneDyn2Out mOneOut = mRt.getOne(String.valueOf(mOutIndex));
                            mTmpSql.append(mOneOut.getSubSqlInfo());
                        } else {
                            Map<String, Object> mOutInfo;
                            if (rootDSL == null) {
                                mOutInfo = StaticMethod.getOneMapByKey(oneSqlPara, "dsls");
                            } else {
                                mOutInfo = rootDSL;
                            }
                            if (mOutInfo.containsKey(String.valueOf(mOutIndex))) {
                                List<Map<String, Object>> mOneDsl = null;
                                try {
                                    mOneDsl = (List<Map<String, Object>>) mOutInfo.get(String.valueOf(mOutIndex));
                                } catch (Exception e) {
                                    log.error(logk.getLogStr() + " SQL 片断参数格式错误" + e.toString(), e);
                                } catch (Error e) {
                                    log.error(logk.getLogStr() + " SQL 片断参数格式错误" + e.toString(), e);
                                }
                                if (mOneDsl == null) {
                                } else {
                                    OneDyn2Out mOneOut = new OneDyn2Out();
                                    Result mRs = mOneOut.NewOneOut(versionId, String.valueOf(mOutIndex), mOneDsl, vOneStaff, epIdInStr, dbType, logk);
                                    if (mRs.getCode() > 0) {
                                        mTmpSql.append(mOneOut.getSubSqlInfo());
                                        mRt.AddOneOut(String.valueOf(mOutIndex), mOneOut);
                                    }
                                }
                            } else {
                                //无片断参数
                            }
                        }
                    }
                } else {
                    mTmpSql.append(mTmp);
                }
            } else {
                mTmpSql.append(mTmp);
            }

        }
        if (dbType == null) {//为SQL 片断中拼接
            mRt.setSqlInfo(mTmpSql.toString());
        } else {
            if (dbType.equalsIgnoreCase(IECDS.MySql) || dbType.equalsIgnoreCase(IECDS.OBMySql)) {
                mRt.setSqlInfo(mTmpSql.toString().replaceAll("\\\\", "\\\\\\\\"));
            } else {
                mRt.setSqlInfo(mTmpSql.toString());
            }
        }
        return mRt;
    }

    private String setBoundDataStr(String sql, Map<String, Object> vPara, Map<String, OneDyn2Out> outBound, LogObj logk) {
        int iTmpIndex = -1, iBeginIndex = 0;
        int i, iCount = _BoundList.size();
        for (i = 0; i < iCount; i++) {
            OneBoundType mTmp1 = _BoundList.get(i);
            String mTmp = StaticMethod.getMapOneString(vPara, mTmp1.getBoundName());
            switch (mTmp1.getBoundType()) {
                default:
                case 0:
                    iTmpIndex = sql.indexOf("?", iBeginIndex);
                    if (iTmpIndex > 0) {
                        sql = sql.substring(0, iTmpIndex) + "'" + mTmp + "'" + sql.substring(iTmpIndex + 1);
                        iBeginIndex = iTmpIndex + mTmp.length() + 2;
                    }
                    break;
                case 1:
                    iTmpIndex = sql.indexOf("?", iBeginIndex);
                    if (iTmpIndex > 0) {
                        sql = sql.substring(0, iTmpIndex) + mTmp + sql.substring(iTmpIndex + 1);
                        iBeginIndex = iTmpIndex + mTmp.length();
                    }
                    break;
                case 5:
                    iTmpIndex = sql.indexOf("?", iBeginIndex);
                    if (iTmpIndex > 0) {
                        sql = sql.substring(0, iTmpIndex) + mTmp + sql.substring(iTmpIndex + 1);
                        iBeginIndex = iTmpIndex + mTmp.length();
                    }
                    break;
                case 2:
                    if (mTmp == null) {
                        mTmp = "";
                    }
                    mTmp = mTmp.trim();
                    if (mTmp.length() == 0) {
                        iTmpIndex = sql.indexOf("?", iBeginIndex);
                        if (iTmpIndex > 0) {
                            sql = sql.substring(0, iTmpIndex) + "Null" + sql.substring(iTmpIndex + 1);
                            iBeginIndex = iTmpIndex + 4;
                        }
                    } else {
                        iTmpIndex = sql.indexOf("?", iBeginIndex);
                        if (iTmpIndex > 0) {
                            sql = sql.substring(0, iTmpIndex) + "'" + mTmp + "'" + sql.substring(iTmpIndex + 1);
                            iBeginIndex = iTmpIndex + mTmp.length() + 2;
                        }
                    }
                    break;

            }
        }
        return sql;
    }

    private int setBoundDataSub(OneStaff staff, List<OneDyn2OutSub> boundList, Map<String, Object> parVal, int iF, StringBuffer vSqlLog, PreparedStatement vSqlObj, LogObj logk) {
        int i, iCount = boundList.size();
        for (i = 0; i < iCount; i++) {
            OneDyn2OutSub mTmpOneSub = boundList.get(i);
            Map<String, Object> valueList = mTmpOneSub.getPara();
            int jCount = mTmpOneSub.getBoundList().size();
            for (int j = 0; j < jCount; j++) {
                OneBoundType mTmp1 = mTmpOneSub.getBoundList().get(j);
                String mTmp = StaticMethod.getSysBySqlMapSubSql(staff, valueList, parVal, mTmp1.getBoundName());//   //StaticMethod.getSysBySqlMap(staff,valueList, mTmp1.getBoundName());
                if (iF == 1) {
                    vSqlLog.append("{ \"v\":\"");
                    vSqlLog.append(StringEscapeUtils.escapeJson(mTmp)).append("\",\"t\":").append(mTmp1.getBoundType()).append("}");
                } else {
                    vSqlLog.append(",{ \"v\":\"");
                    vSqlLog.append(StringEscapeUtils.escapeJson(mTmp)).append("\",\"t\":").append(mTmp1.getBoundType()).append("}");
                }
                switch (mTmp1.getBoundType()) {
                    default:
                    case 0:
                        try {
                            //	log.info(logk.getLogStr()+ " 执行SQL 失败，字段 " +iF+":"+mTmp1.getBoundName()+" Value:"+mTmp);
                            vSqlObj.setString(iF, mTmp);
                            iF++;
                        } catch (Exception e) {
                            log.error(logk.getLogStr() + " 执行SQL 失败，字段 " + iF + ":" + mTmp1.getBoundName() + " Value:" + mTmp, e);
                            return -1;
                        }
                        break;
                    case 1:
                        try {
                            //	log.info(logk.getLogStr()+ " 执行SQL 失败，字段 " +iF+":"+mTmp1.getBoundName()+" Value:"+mTmp);
                            vSqlObj.setBigDecimal(iF, StaticMethod.StringToBigDec(mTmp));
                            iF++;
                        } catch (Exception e) {
                            log.error(logk.getLogStr() + " 执行SQL 失败，字段 " + iF + ":" + mTmp1.getBoundName() + " Value:" + mTmp, e);
                            return -1;
                        }
                        break;
                    case 5:
                        try {
                            //	log.info(logk.getLogStr()+ " 执行SQL 失败，字段 " +iF+":"+mTmp1.getBoundName()+" Value:"+mTmp);
                            vSqlObj.setInt(iF, StaticMethod.StringToInt(mTmp));
                            iF++;
                        } catch (Exception e) {
                            log.error(logk.getLogStr() + " 执行SQL 失败，字段 " + iF + ":" + mTmp1.getBoundName() + " Value:" + mTmp, e);
                            return -1;
                        }
                        break;
                    case 2:
                        if (mTmp == null) {
                            mTmp = "";
                        }
                        mTmp = mTmp.trim();
                        if (mTmp.length() == 0) {
                            try {
                                //	log.info(logk.getLogStr()+ " 执行SQL 失败，字段 " +iF+":"+mTmp1.getBoundName()+" Value:"+mTmp);
                                vSqlObj.setTimestamp(iF, null);
                                iF++;
                            } catch (Exception e) {
                                log.error(logk.getLogStr() + " 执行SQL 失败，字段 " + iF + ":" + mTmp1.getBoundName() + " Value is Null", e);
                                return -1;
                            }
                        } else {

                            try {

                                // 时间格式变量处理
                                log.debug("setBoundDataSub 时间格式变量处理 mTmp:" + mTmp);
                                Timestamp mTmpV = StaticMethod.ValtoSqlTimeStamp(mTmp, logk);
                                vSqlObj.setTimestamp(iF, mTmpV);
                                log.debug("setBoundDataSub 时间格式变量处理 mTmp End");
                                iF++;
                            } catch (Exception e) {
                                log.error(logk.getLogStr() + " 执行SQL 失败，字段 " + iF + ":" + mTmp1.getBoundName() + " Value:" + mTmp, e);
                                return -1;
                            }

                        }
                        break;
                }
            }
        }
        return iF;
    }

    private int setBoundData(String pgId, String sqlId, OneStaff staff, PreparedStatement vSqlObj, Map<String, Object> vValueList, Map<String, OneDyn2Out> outBound, LogObj logk, String vBuildSql) {
        int i, iCount = _BoundList.size();
        StringBuffer mTmpLog = new StringBuffer("Para:[");
        int iF = 1;
        for (i = 0; i < iCount; i++) {
            OneBoundType mTmp1 = _BoundList.get(i);
            String mTmp = StaticMethod.getSysBySqlMap(staff, vValueList, mTmp1.getBoundName());
            if (mTmp1.getBoundType() != 100) {
                if (iF == 1) {
                    mTmpLog.append("{ \"v\":\"");//\"").append(mTmp1.getBoundName()).append("\":
                    mTmpLog.append(StringEscapeUtils.escapeJson(mTmp)).append("\",\"t\":").append(mTmp1.getBoundType()).append("}");
                } else {
                    mTmpLog.append(",{ \"v\":\"");//\"").append(mTmp1.getBoundName()).append("\":
                    mTmpLog.append(StringEscapeUtils.escapeJson(mTmp)).append("\",\"t\":").append(mTmp1.getBoundType()).append("}");
                }
            }
            switch (mTmp1.getBoundType()) {
                default:
                case 0:
                    try {
                        //log.info(logk.getLogStr()+ " 执行SQL 失败，字段 " +iF+":"+mTmp1.getBoundName()+" Value:"+mTmp);
                        vSqlObj.setString(iF, mTmp);
                        iF++;
                    } catch (Exception e) {
                        log.error(logk.getLogStr() + "sqlId:" + sqlId + "   执行SQL 失败，字段 " + iF + ":" + mTmp1.getBoundName() + " Value:" + mTmp, e);
                        return -1;
                    }
                    break;
                case 1:
                    try {
                        //log.info(logk.getLogStr()+ " 执行SQL 失败，字段 " +iF+":"+mTmp1.getBoundName()+" Value:"+mTmp);
                        vSqlObj.setBigDecimal(iF, StaticMethod.StringToBigDec(mTmp));
                        iF++;
                        //vSqlObj.setFloat(i+1,StaticMethod.StringToFloat());
                    } catch (Exception e) {
                        log.error(logk.getLogStr() + "sqlId:" + sqlId + "   执行SQL 失败，字段 " + iF + ":" + mTmp1.getBoundName() + " Value:" + mTmp, e);
                        return -1;
                    }
                    break;
                case 5:
                    try {
                        //log.info(logk.getLogStr()+ " 执行SQL 失败，字段 " +iF+":"+mTmp1.getBoundName()+" Value:"+mTmp);
                        vSqlObj.setInt(iF, StaticMethod.StringToInt(mTmp));
                        iF++;
                        //vSqlObj.setFloat(i+1,StaticMethod.StringToFloat());
                    } catch (Exception e) {
                        log.error(logk.getLogStr() + "sqlId:" + sqlId + "   执行SQL 失败，字段 " + iF + ":" + mTmp1.getBoundName() + " Value:" + mTmp, e);
                        return -1;
                    }
                    break;
                case 2:
                    if (mTmp == null) {
                        mTmp = "";
                    }
                    mTmp = mTmp.trim();
                    if (mTmp.length() == 0) {
                        try {
                            //log.info(logk.getLogStr()+ " 执行SQL 失败，字段 " +iF+":"+mTmp1.getBoundName()+" Value:"+mTmp);
                            vSqlObj.setTimestamp(iF, null);
                            iF++;
                        } catch (Exception e) {
                            log.error(logk.getLogStr() + "sqlId:" + sqlId + "   执行SQL 失败，字段 " + iF + ":" + mTmp1.getBoundName() + " Value is Null", e);
                            return -1;
                        }
                    } else {

                        try {
                            // 时间格式变量处理
                            log.debug("setBoundData 时间格式变量处理 mTmp:" + mTmp);
                            Timestamp mTmpV = StaticMethod.ValtoSqlTimeStamp(mTmp, logk);
                            vSqlObj.setTimestamp(iF, mTmpV);
                            log.debug("setBoundData 时间格式变量处理 mTmp End");
                            iF++;
                        } catch (Exception e) {
                            log.error(logk.getLogStr() + "sqlId:" + sqlId + "  执行SQL 失败，字段 " + iF + ":" + mTmp1.getBoundName() + " Value:" + mTmp, e);
                            return -1;
                        }

                    }
                    break;
                case 100:
                    String mOutKey = mTmp1.getBoundName();
                    if (outBound.containsKey(mOutKey)) {
                        OneDyn2Out mTmpOneOut = outBound.get(mOutKey);
                        iF = setBoundDataSub(staff, mTmpOneOut.getBoundList(), vValueList, iF, mTmpLog, vSqlObj, logk);
                        if (iF < 1) {
                            log.error(logk.getLogStr() + "sqlId:" + sqlId + "   执行SQL 失败，SQL 片断设值失败");
                            return -1;
                        }
                    }
                    break;
            }
        }
        mTmpLog.append("]");
        //		log.MDCAdd("tag", sqlTypeList.systemSql);
        if (PublicVar.getInstance().getLogSqlJM() == 1) {
            log.info(logk.getLogStr() + " LastBoundSql  pageId:" + pgId + " sqlid:" + sqlId + " staffId:" + staff.getStaffId() + "   sql : " + vBuildSql + " Para:" + DES.encrypt(mTmpLog.substring(5), PublicVar.getInstance().getLogSqlKey(), logk));
        } else {
            log.info(logk.getLogStr() + " LastBoundSql  pageId:" + pgId + " sqlid:" + sqlId + " staffId:" + staff.getStaffId() + "   sql : " + vBuildSql + " " + mTmpLog.toString());
        }
        if (PublicVar.getInstance().getLogSqlFlag() == 1) {
            log.info(logk.getLogStr() + " LastSql  pageId:" + pgId + " sqlid:" + sqlId + " staffId:" + staff.getStaffId() + "   sql : " + setBoundDataStr(vBuildSql, vValueList, outBound, logk));
        }
        //		log.MDCRemove("tag");
        return 1;
    }

    public ResultSetObjList doSelect(String logPgId, String logSqlId, String versionId, Connection vConn, Map<String, Object> vValueList, OneDyn2Sqls OneDynRoot, Map<String, Object> oneSqlPara, OneStaff vOneStaff, String epIdInStr, int vMaxRow, String dbType, Map<String, Object> rootDSL, LogObj logk, int vFlag) {
        ReturnDyn2OneSql rtSql = ReplacePara(versionId, vValueList, OneDynRoot, oneSqlPara, vOneStaff, epIdInStr, dbType, rootDSL, logk);
        if (_BoundList.size() > 0) {
            //log.info(logk.getLogStr()+" Do Select Bound Sql:"+mBuildSql+ " Values:"+vValueList.toString());
            PreparedStatement RsState = DBMethod.CreatePreParedStatementReadOnly(vConn, rtSql.getSqlInfo(), logk);
            if (vMaxRow > 0) {
                if (vFlag == 1) {
                    try {
                        RsState.setMaxRows(vMaxRow);
                    } catch (Exception e) {
                        log.info(logk.getLogStr() + " Set Max Row Failed:" + rtSql.getSqlInfo());
                    }
                }
            } else if (vMaxRow <= 0) {
                if (vFlag == 1) {
                    if (vMaxRow == -99) {
                        log.debug(logk.getLogStr() + " Set Max Row 不限制 ");
                    } else {
                        try {
                            RsState.setMaxRows(PublicVar.getInstance().get_DefaultMaxRow());
                        } catch (Exception e) {
                            log.info(logk.getLogStr() + " Set Max Row Failed:" + rtSql.getSqlInfo());
                        }
                    }
                }
            }
            if (setBoundData(logPgId, logSqlId, vOneStaff, RsState, vValueList, rtSql.getBoundList(), logk, rtSql.getSqlInfo()) > 0) {
                ResultSet mTmpRs;
                try {
                    mTmpRs = RsState.executeQuery();
                } catch (Exception e) {
                    log.error(logk.getLogStr() + " Query Sql 打开失败  pageId:" + logPgId + " sqlid:" + logSqlId + " staffId:" + vOneStaff.getStaffId() + "  sql:" + rtSql.getSqlInfo(), e);
                    DBMethod.ClosePreparedStatement(RsState);
                    ErrManager.getInstance().AddOneMsgSql(1, "DB", "Select Sql  pageId:" + logPgId + " sqlid:" + logSqlId + " staffId:" + vOneStaff.getStaffId() + "  sql:" + rtSql.getSqlInfo() + " Err:" + e.toString());
                    return null;
                } catch (Error e) {
                    log.error(logk.getLogStr() + " Query Sql 打开失败   pageId:" + logPgId + " sqlid:" + logSqlId + " staffId:" + vOneStaff.getStaffId() + "  sql:" + rtSql.getSqlInfo(), e);
                    DBMethod.ClosePreparedStatement(RsState);
                    ErrManager.getInstance().AddOneMsgSql(1, "DB", "Select Sql  pageId:" + logPgId + " sqlid:" + logSqlId + " staffId:" + vOneStaff.getStaffId() + "  sql:" + rtSql.getSqlInfo() + " Err:" + e.toString());
                    return null;
                }
                log.debug(logk.getLogStr() + " Query Sql  Opened Succ    pageId:" + logPgId + " staffId:" + vOneStaff.getStaffId() + " sqlid:" + logSqlId);

                return new ResultSetObjList(RsState, mTmpRs, new MonitorOne(1, rtSql.getSqlInfo(), vValueList, vOneStaff.UseDBName(""), vOneStaff.getLoginKey(), vOneStaff.getStaffId(), vOneStaff.getStaffName(), vOneStaff.getLoginID(), 1));
            } else {
                log.info(logk.getLogStr() + " Query Sql   pageId:" + logPgId + " sqlid:" + logSqlId + " staffId:" + vOneStaff.getStaffId() + "  sql:" + "  " + rtSql.getSqlInfo() + " : Opened Failed");
                DBMethod.ClosePreparedStatement(RsState);
                return null;
            }
        } else {
            //log.info(logk.getLogStr()+" Do Select Sql:"+mBuildSql);
            Statement mTmp = DBMethod.CreateStatementReadOnlyNext(vConn);
            if (vMaxRow > 0) {
                if (vFlag == 1) {
                    try {
                        mTmp.setMaxRows(vMaxRow);
                    } catch (Exception e) {
                        log.info(logk.getLogStr() + " Set Max Row Failed:" + rtSql.getSqlInfo());
                    }
                }
            } else if (vMaxRow <= 0) {
                if (vMaxRow == -99) {
                    log.debug(logk.getLogStr() + " Set Max Row 不限制 ");
                } else {
                    try {
                        mTmp.setMaxRows(PublicVar.getInstance().get_DefaultMaxRow());
                    } catch (Exception e) {
                        log.info(logk.getLogStr() + " Set Max Row Failed:" + rtSql.getSqlInfo());
                    }
                }
            }
            return new ResultSetObjList(mTmp, DBMethod.getResultSetDyn3(logPgId, logSqlId, vOneStaff.getStaffId(), mTmp, rtSql.getSqlInfo(), sqlTypeList.dynSql, logk), new MonitorOne(1, rtSql.getSqlInfo(), new HashMap<String, Object>(), vOneStaff.UseDBName(""), vOneStaff.getLoginKey(), vOneStaff.getStaffId(), vOneStaff.getStaffName(), vOneStaff.getLoginID(), 1));
        }
    }

    public Result doExec(String pageId, String sqlId, String versionId, Connection vConn, Statement vStatement, Map<String, Object> vValueList, OneDyn2Sqls OneDynRoot, Map<String, Object> oneSqlPara, OneStaff vOneStaff, String epIdInStr, String vDBType, Map<String, Object> rootDSL, LogObj logk) {
        ReturnDyn2OneSql rtSql = ReplacePara(versionId, vValueList, OneDynRoot, oneSqlPara, vOneStaff, epIdInStr, vDBType, rootDSL, logk);
        if (_BoundList.size() > 0) {
            //log.info(logk.getLogStr()+" Do Exec Bound Sql:"+mBuildSql+ " Values:"+vValueList.toString());
            PreparedStatement PreInsert = DBMethod.CreatePreParedStatementUpdate(vConn, rtSql.getSqlInfo(), logk);
            if (setBoundData(pageId, sqlId, vOneStaff, PreInsert, vValueList, rtSql.getBoundList(), logk, rtSql.getSqlInfo()) > 0) {
                int i = 0;
                try {
                    i = PreInsert.executeUpdate();
                } catch (Exception e) {
                    log.error(logk.getLogStr() + " Sql 执行失败  pageId:" + pageId + " sqlid:" + sqlId + " staffId:" + vOneStaff.getStaffId() + "  sql:" + rtSql.getSqlInfo(), e);
                    DBMethod.ClosePreparedStatement(PreInsert);
                    ErrManager.getInstance().AddOneMsgSql(1, "DB", "Do Sql  pageId:" + pageId + " sqlid:" + sqlId + " staffId:" + vOneStaff.getStaffId() + "  sql:" + rtSql.getSqlInfo() + " Err:" + e.toString());
                    return new Result(-1, " Sql 执行失败: Err:" + e.toString());//"+rtSql.getSqlInfo()+"
                } catch (Error e) {
                    log.error(logk.getLogStr() + " Sql 执行失败   pageId:" + pageId + " sqlid:" + sqlId + " staffId:" + vOneStaff.getStaffId() + "  sql:" + rtSql.getSqlInfo(), e);
                    DBMethod.ClosePreparedStatement(PreInsert);
                    ErrManager.getInstance().AddOneMsgSql(1, "DB", "Do Sql pageId:" + pageId + " sqlid:" + sqlId + " staffId:" + vOneStaff.getStaffId() + "  sql:" + rtSql.getSqlInfo() + " Err:" + e.toString());
                    return new Result(-1, " Sql 执行失败:Err:" + e.toString()); //"+rtSql.getSqlInfo()+"
                }
                log.info(logk.getLogStr() + " Do Sql   pageId:" + pageId + " sqlid:" + sqlId + " staffId:" + vOneStaff.getStaffId() + "  sql:" + rtSql.getSqlInfo() + " Value:" + i);
                DBMethod.ClosePreparedStatement(PreInsert);
                return new Result(i);
            } else {
                log.info(logk.getLogStr() + " Do Sql   pageId:" + pageId + " sqlid:" + sqlId + " staffId:" + vOneStaff.getStaffId() + "  sql:" + rtSql.getSqlInfo() + " Value:-1");
                DBMethod.ClosePreparedStatement(PreInsert);
                return new Result(-1, " Sql 执行失败: 绑定值设置失败");//"+rtSql.getSqlInfo()+"
            }
        } else {
            //log.info("S:"+StaticMethod.getThreadID()+" Do Exec Sql:"+mBuildSql);
            return DBMethod.ExecSqlbyDyn3(pageId, sqlId, vOneStaff.getStaffId(), vStatement, rtSql.getSqlInfo(), sqlTypeList.systemSql, logk);
        }
    }

    public Result DoExec(String pageId, String sqlId, String versionId, Connection vConn, Map<String, Object> vValueList, OneDyn2Sqls OneDynRoot, Map<String, Object> oneSqlPara, OneStaff vOneStaff, String epIdInStr, String vDBType, Map<String, Object> rootDSL, LogObj logk) {
        long ccc = System.currentTimeMillis();
        ReturnDyn2OneSql rtSql = ReplacePara(versionId, vValueList, OneDynRoot, oneSqlPara, vOneStaff, epIdInStr, vDBType, rootDSL, logk);//=ReplacePara(vValueList,oneSqlPara,vOneStaff);
        if (_BoundList.size() > 0) {
            //log.info(logk.getLogStr()+" Do Exec Bound Sql:"+mBuildSql+"  Values:"+vValueList.toString());
            PreparedStatement PreInsert = DBMethod.CreatePreParedStatementUpdate(vConn, rtSql.getSqlInfo(), logk);
            if (setBoundData(pageId, sqlId, vOneStaff, PreInsert, vValueList, rtSql.getBoundList(), logk, rtSql.getSqlInfo()) > 0) {
                int i = 0;
                try {
                    i = PreInsert.executeUpdate();
                } catch (Exception e) {
                    log.error(logk.getLogStr() + " Sql 执行失败   pageId:" + pageId + " sqlid:" + sqlId + " staffId:" + vOneStaff.getStaffId() + "  sql:" + rtSql.getSqlInfo(), e);
                    DBMethod.ClosePreparedStatement(PreInsert);
                    ErrManager.getInstance().AddOneMsgSql(1, "DB", "Do Sql pageId:" + pageId + " sqlid:" + sqlId + " staffId:" + vOneStaff.getStaffId() + "  sql:" + rtSql.getSqlInfo() + " Err:" + e.toString());
                    return new Result(-1, " Sql 执行失败: Err:" + e.toString());//"+rtSql.getSqlInfo()+"
                } catch (Error e) {
                    log.error(logk.getLogStr() + " Sql 执行失败 pageId:" + pageId + " sqlid:" + sqlId + " staffId:" + vOneStaff.getStaffId() + "  sql:" + rtSql.getSqlInfo(), e);
                    DBMethod.ClosePreparedStatement(PreInsert);
                    ErrManager.getInstance().AddOneMsgSql(1, "DB", "Do Sql pageId:" + pageId + " sqlid:" + sqlId + " staffId:" + vOneStaff.getStaffId() + "  sql:" + rtSql.getSqlInfo() + " Err:" + e.toString());
                    return new Result(-1, " Sql 执行失败: Err:" + e.toString());//"+rtSql.getSqlInfo()+"
                }
                log.info(logk.getLogStr() + " Do Sql pageId:" + pageId + " sqlid:" + sqlId + " staffId:" + vOneStaff.getStaffId() + "  sql:" + rtSql.getSqlInfo() + " Value:" + i);
                DBMethod.ClosePreparedStatement(PreInsert);
                MonitorRoot.getInstance().AddOne(0, rtSql.getSqlInfo(), i, vValueList, System.currentTimeMillis() - ccc, vOneStaff.UseDBName(""), vOneStaff.getLoginKey(), vOneStaff.getStaffId(), vOneStaff.getStaffName(), vOneStaff.getLoginID(), 1);
                return new Result(1);
            } else {
                log.info(logk.getLogStr() + " Do Sql:" + rtSql.getSqlInfo() + " Value:-1");
                DBMethod.ClosePreparedStatement(PreInsert);
                return new Result(-1, " Sql 执行失败:绑定值设置失败"); //"+rtSql.getSqlInfo()+"
            }
        } else {
            log.debug(logk.getLogStr() + " Do Exec Sql pageId:" + pageId + " sqlid:" + sqlId + " staffId:" + vOneStaff.getStaffId() + "  sql:" + rtSql.getSqlInfo());
            Statement mTmp = DBMethod.CreateStatementUpdate(vConn);
            Result i = DBMethod.ExecSqlbyDyn3(pageId, sqlId, vOneStaff.getStaffId(), mTmp, rtSql.getSqlInfo(), sqlTypeList.systemSql, logk);
            log.info(logk.getLogStr() + " Do Exec Sql pageId:" + pageId + " sqlid:" + sqlId + " staffId:" + vOneStaff.getStaffId() + "  sql:" + rtSql.getSqlInfo() + " Result:" + i.toString());
            DBMethod.CloseStatement(mTmp);
            MonitorRoot.getInstance().AddOne(0, rtSql.getSqlInfo(), i.getCode(), vValueList, System.currentTimeMillis() - ccc, vOneStaff.UseDBName(""), vOneStaff.getLoginKey(), vOneStaff.getStaffId(), vOneStaff.getStaffName(), vOneStaff.getLoginID(), 1);
            return i;
        }
    }
}
